﻿GO
/****** Object:  Table [dbo].[Cms_Sys_Mail]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cms_Sys_Mail](
	[MailMsgID] [uniqueidentifier] NOT NULL,
	[Receivers] [varchar](5000) NULL,
	[CCReceivers] [varchar](5000) NULL,
	[Subject] [varchar](256) NULL,
	[Body] [ntext] NULL,
	[Status] [int] NULL,
	[CreateTime] [datetime] NULL,
	[ReceiverDisplayName] [nvarchar](50) NULL,
	[CCReceiverDisplayName] [nvarchar](50) NULL,
	[CreatedOn] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[LastModifyOn] [datetime] NULL,
	[LastModifyBy] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Sys_Mail] PRIMARY KEY CLUSTERED 
(
	[MailMsgID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Sys_Log]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Sys_Log](
	[logGUID] [uniqueidentifier] NOT NULL,
	[LogType] [int] NULL,
	[KeyWord] [nvarchar](50) NULL,
	[Operator] [nvarchar](50) NULL,
	[OperatorIP] [nvarchar](50) NULL,
	[Remark] [nvarchar](500) NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_TM_Sys_Log] PRIMARY KEY CLUSTERED 
(
	[logGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Sys_Function]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Sys_Function](
	[FuncID] [nvarchar](10) NOT NULL,
	[FFuncID] [nvarchar](10) NULL,
	[FuncName] [nvarchar](50) NULL,
	[IsMenu] [int] NULL,
	[URL] [nvarchar](250) NULL,
	[ImageUrl] [nvarchar](250) NULL,
	[Target] [nvarchar](50) NULL,
	[Description] [nvarchar](250) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_TM_SYS_FUNCTION] PRIMARY KEY CLUSTERED 
(
	[FuncID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Sys_Dictionary]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Sys_Dictionary](
	[ID] [uniqueidentifier] NOT NULL,
	[Type] [int] NULL,
	[Name] [nvarchar](256) NULL,
	[ParentID] [uniqueidentifier] NULL,
	[Code] [nvarchar](256) NULL,
	[Value] [decimal](18, 2) NULL,
	[Description] [nvarchar](1024) NULL,
	[CreatedOn] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
	[Level] [int] NULL,
	[BusinessType] [int] NULL,
 CONSTRAINT [TC_Sys_Dictionary_PK] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Sys_DesktopWebParts]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[Cms_Sys_DesktopWebParts](
	[WPCode] [varchar](16) NULL,
	[WpType] [varchar](16) NULL,
	[URL] [varchar](100) NULL,
	[Height] [varchar](8) NULL,
	[DesktopID] [uniqueidentifier] NULL,
	[Area] [tinyint] NULL,
	[Sequence] [tinyint] NULL,
	[Target] [varchar](16) NULL,
	[Expanded] [tinyint] NULL,
	[RowID] [uniqueidentifier] NOT NULL,
	[WPName] [varchar](50) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Desktop_WebParts] PRIMARY KEY CLUSTERED 
(
	[RowID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Sys_Desktop]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Sys_Desktop](
	[DesktopID] [uniqueidentifier] NOT NULL,
	[IsPublic] [bit] NULL,
	[IsDefault] [bit] NULL,
	[Owner] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[ModifiedOn] [datetime] NULL,
	[DesktopName] [nvarchar](50) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Sys_Desktop] PRIMARY KEY CLUSTERED 
(
	[DesktopID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Sys_Department]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cms_Sys_Department](
	[DepartmentID] [uniqueidentifier] NOT NULL,
	[DepartmentName] [nvarchar](50) NULL,
	[DepartmentCode] [nvarchar](50) NULL,
	[HierarchyCode] [nvarchar](500) NULL,
	[ParentID] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[ModifiedOn] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[Comments] [varchar](200) NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[level] [int] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_myDepartment] PRIMARY KEY CLUSTERED 
(
	[DepartmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Rpt_PutTrackStatistics]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cms_Rpt_PutTrackStatistics](
	[RID] [uniqueidentifier] NOT NULL,
	[CustomerID] [uniqueidentifier] NOT NULL,
	[InstrumentCategoryName] [nvarchar](20) NOT NULL,
	[TrackYear] [char](4) NOT NULL,
	[TrackMonth] [char](2) NOT NULL,
	[PlanSaleAmount] [decimal](18, 2) NULL,
	[FactSaleAmount] [decimal](18, 2) NULL,
	[FactSaleTrackURL] [nvarchar](255) NULL,
 CONSTRAINT [PK_Cms_rpt_PutTrackStatistics] PRIMARY KEY CLUSTERED 
(
	[RID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Rpt_PutContractPlan]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cms_Rpt_PutContractPlan](
	[RID] [uniqueidentifier] NOT NULL,
	[CustomerID] [uniqueidentifier] NOT NULL,
	[ContractID] [uniqueidentifier] NOT NULL,
	[InstrumentCategoryName] [nvarchar](20) NOT NULL,
	[TrackYear] [char](4) NOT NULL,
	[TrackMonth] [char](2) NOT NULL,
	[PlanSaleAmount] [money] NULL,
 CONSTRAINT [PK_Cms_rpt_PutContractPlan] PRIMARY KEY CLUSTERED 
(
	[RID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Ctrt_MyNoticeUser]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_MyNoticeUser](
	[ID] [uniqueidentifier] NOT NULL,
	[UserID] [uniqueidentifier] NULL,
	[NoticeID] [uniqueidentifier] NULL,
	[Status] [int] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Ctrt_MyNoticeUser] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Ctrt_MyNotice]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_MyNotice](
	[NoticeID] [uniqueidentifier] NOT NULL,
	[NoticeContent] [nvarchar](1000) NULL,
	[NoticeTime] [datetime] NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
	[NoticeType] [int] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Cms_Ctrt_MyNotice] PRIMARY KEY CLUSTERED 
(
	[NoticeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Ctrt_InstrumentCategory]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_InstrumentCategory](
	[categoryGUID] [uniqueidentifier] NOT NULL,
	[categoryCode] [nvarchar](50) NULL,
	[categoryName] [nvarchar](50) NULL,
	[isLastLevel] [int] NULL,
	[parentGUID] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_bus_m_InstrumentCategory] PRIMARY KEY CLUSTERED 
(
	[categoryGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Ctrt_Instrument]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_Instrument](
	[instrumentGUID] [uniqueidentifier] NOT NULL,
	[instrumentCode] [nvarchar](128) NULL,
	[instrumentName] [nvarchar](128) NULL,
	[instrumentStandard] [nvarchar](128) NULL,
	[unit] [nvarchar](50) NULL,
	[vendor] [nvarchar](50) NULL,
	[CreatedOn] [datetime] NULL,
	[categoryGUID] [nvarchar](50) NULL,
	[Type] [int] NULL,
	[Deleted] [bit] NULL,
	[CategoryCode] [varchar](20) NULL,
	[Cost] [decimal](18, 2) NULL,
	[PresellPrice] [decimal](18, 2) NULL,
 CONSTRAINT [PK_bus_m_instrument] PRIMARY KEY CLUSTERED 
(
	[instrumentGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Ctrt_Document]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_Document](
	[DocumentID] [uniqueidentifier] NOT NULL,
	[ContractID] [uniqueidentifier] NULL,
	[DocumentName] [varchar](50) NULL,
	[FileName] [nvarchar](256) NULL,
	[FileOriginalName] [nvarchar](256) NULL,
	[FilePath] [nvarchar](256) NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedName] [nvarchar](50) NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_bus_m_document] PRIMARY KEY CLUSTERED 
(
	[DocumentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Ctrt_CustomerUser]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_CustomerUser](
	[ID] [uniqueidentifier] NOT NULL,
	[CustomerID] [uniqueidentifier] NULL,
	[UserID] [uniqueidentifier] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedAt] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Ctrt_CustomerUser] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Ctrt_Customer]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_Customer](
	[CustomerID] [uniqueidentifier] NOT NULL,
	[CustomerName] [sysname] NULL,
	[CustomerAddress] [nvarchar](300) NULL,
	[Contactor] [nvarchar](50) NULL,
	[Contactphone] [nvarchar](50) NULL,
	[Salesman] [nvarchar](50) NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
	[CustomerCode] [varchar](20) NULL,
 CONSTRAINT [PK_bus_m_customer] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Ctrt_ContractPayment]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_ContractPayment](
	[PaymentID] [uniqueidentifier] NOT NULL,
	[ContractID] [uniqueidentifier] NULL,
	[PayDate] [datetime] NULL,
	[PayAmount] [decimal](18, 2) NULL,
	[CreatedAt] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Ctrt_ContractPayment] PRIMARY KEY CLUSTERED 
(
	[PaymentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Ctrt_ContractOthorIncomeExtend]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_ContractOthorIncomeExtend](
	[ContractID] [uniqueidentifier] NOT NULL,
	[MachinePeriod] [int] NULL,
	[MachinePerIncome] [decimal](18, 2) NULL,
	[MachineTotalIncome] [decimal](18, 2) NULL,
	[MachineDate] [datetime] NULL,
	[MachineAlertDays] [int] NULL,
	[MachineRemark] [nvarchar](500) NULL,
	[MaintainPeriod] [int] NULL,
	[MaintainPerIncome] [decimal](18, 2) NULL,
	[MaintainTotalIncome] [decimal](18, 2) NULL,
	[MaintainDate] [datetime] NULL,
	[MaintainAlertDays] [int] NULL,
	[MaintainRemark] [nvarchar](500) NULL,
	[RentPeriod] [int] NULL,
	[RentPerIncome] [decimal](18, 2) NULL,
	[RentTotalIncome] [decimal](18, 2) NULL,
	[RentDate] [datetime] NULL,
	[RentAlertDays] [int] NULL,
	[RentRemark] [nvarchar](500) NULL,
	[CreatedAt] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[LastModifiedAt] [datetime] NULL,
	[LastModifiedBy] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Cms_Ctrt_ContractExtend] PRIMARY KEY CLUSTERED 
(
	[ContractID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Ctrt_ContractItemPart]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_ContractItemPart](
	[PartID] [uniqueidentifier] NOT NULL,
	[ItemID] [uniqueidentifier] NULL,
	[InstrumentID] [uniqueidentifier] NULL,
	[Count] [int] NULL,
 CONSTRAINT [PK_Cms_Ctrt_ContractItemPart] PRIMARY KEY CLUSTERED 
(
	[PartID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Wfl_Model]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Wfl_Model](
	[ModelID] [uniqueidentifier] NOT NULL,
	[ModelName] [nvarchar](50) NULL,
	[ModelDescription] [nvarchar](200) NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_CMS_WFL_MODEL] PRIMARY KEY CLUSTERED 
(
	[ModelID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Wfl_Comment]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Wfl_Comment](
	[CommentID] [uniqueidentifier] NOT NULL,
	[InstanceID] [uniqueidentifier] NULL,
	[ActivityID] [uniqueidentifier] NULL,
	[TrackID] [uniqueidentifier] NULL,
	[CommentStatus] [tinyint] NULL,
	[CommentAt] [datetime] NULL,
	[Comments] [ntext] NULL,
	[CreatedAt] [datetime] NULL,
	[LeaderID] [uniqueidentifier] NULL,
	[LeaderName] [nvarchar](500) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Wfl_Comment] PRIMARY KEY CLUSTERED 
(
	[CommentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Wfl_ActivityCheckers]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Wfl_ActivityCheckers](
	[ID] [uniqueidentifier] NOT NULL,
	[ActivityID] [uniqueidentifier] NULL,
	[UserID] [uniqueidentifier] NULL,
	[Account] [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Wfl_ActivityCheckers] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Wfl_Activity]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Wfl_Activity](
	[ActivityID] [uniqueidentifier] NOT NULL,
	[ModelID] [uniqueidentifier] NULL,
	[ActivityName] [nvarchar](50) NULL,
	[ActivityType] [tinyint] NULL,
	[ActivityEntry] [nvarchar](1000) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_CMS_WFL_ACTIVITY] PRIMARY KEY CLUSTERED 
(
	[ActivityID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 Start , 2   Normal ,  4 End' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Cms_Wfl_Activity', @level2type=N'COLUMN',@level2name=N'ActivityType'
GO
/****** Object:  Table [dbo].[Cms_Sys_Webparts]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cms_Sys_Webparts](
	[WPGUID] [uniqueidentifier] NOT NULL,
	[WPCode] [varchar](16) NULL,
	[WPType] [varchar](16) NULL,
	[URL] [varchar](100) NULL,
	[Height] [varchar](8) NULL,
	[Comments] [varchar](200) NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
	[WPName] [nvarchar](50) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Sys_Webparts] PRIMARY KEY CLUSTERED 
(
	[WPGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SaleBillVouchs]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SaleBillVouchs](
	[SBVID] [int] NOT NULL,
	[AutoID] [int] NOT NULL,
	[cWhCode] [varchar](10) NULL,
	[cInvCode] [varchar](20) NOT NULL,
	[iQuantity] [float] NULL,
	[iNum] [float] NULL,
	[iQuotedPrice] [float] NULL,
	[iUnitPrice] [float] NULL,
	[iTaxUnitPrice] [float] NULL,
	[iMoney] [money] NULL,
	[iTax] [money] NULL,
	[iSum] [money] NULL,
	[iDisCount] [money] NULL,
	[iNatUnitPrice] [float] NULL,
	[iNatMoney] [money] NULL,
	[iNatTax] [money] NULL,
	[iNatSum] [money] NULL,
	[iNatDisCount] [money] NULL,
	[iSBVID] [int] NULL,
	[iMoneySum] [float] NULL,
	[iExchSum] [float] NULL,
	[cClue] [varchar](30) NULL,
	[cIncomeSub] [varchar](15) NULL,
	[cTaxSub] [varchar](15) NULL,
	[dSignDate] [datetime] NULL,
	[cMemo] [varchar](60) NULL,
	[iBatch] [int] NULL,
	[cBatch] [varchar](20) NULL,
	[bSettleAll] [bit] NULL,
	[cFree1] [varchar](20) NULL,
	[cFree2] [varchar](20) NULL,
	[RdsID] [int] NULL,
	[iTB] [tinyint] NULL,
	[dvDate] [datetime] NULL,
	[TBQuantity] [real] NULL,
	[TBNum] [real] NULL,
	[iSOsID] [int] NULL,
	[iDLsID] [int] NULL,
	[KL] [float] NULL,
	[KL2] [float] NULL,
	[cInvName] [varchar](60) NULL,
	[iTaxRate] [float] NULL,
	[cDefine22] [varchar](60) NULL,
	[cDefine23] [varchar](60) NULL,
	[cDefine24] [varchar](60) NULL,
	[cDefine25] [varchar](60) NULL,
	[cDefine26] [float] NULL,
	[cDefine27] [float] NULL,
	[fOutQuantity] [float] NULL,
	[fOutNum] [float] NULL,
	[cItemCode] [varchar](20) NULL,
	[cItem_class] [varchar](10) NULL,
	[fSaleCost] [float] NULL,
	[fSalePrice] [float] NULL,
	[cItemName] [varchar](60) NULL,
	[cItem_CName] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SaleBillVouch]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SaleBillVouch](
	[SBVID] [int] NOT NULL,
	[cSBVCode] [varchar](10) NOT NULL,
	[cVouchType] [varchar](2) NOT NULL,
	[cSTCode] [varchar](2) NULL,
	[dDate] [datetime] NOT NULL,
	[cSaleOut] [varchar](255) NULL,
	[cRdCode] [varchar](5) NULL,
	[cDepCode] [varchar](12) NOT NULL,
	[cPersonCode] [varchar](8) NULL,
	[cSOCode] [varchar](10) NULL,
	[cCusCode] [varchar](20) NULL,
	[cPayCode] [varchar](3) NULL,
	[cexch_name] [varchar](8) NOT NULL,
	[cMemo] [varchar](60) NULL,
	[iExchRate] [float] NULL,
	[iTaxRate] [float] NULL,
	[bReturnFlag] [bit] NOT NULL,
	[cBCode] [varchar](3) NULL,
	[cBillVer] [varchar](2) NULL,
	[cVoucherType] [varchar](50) NULL,
	[cVoucherCode] [varchar](50) NULL,
	[cMaker] [varchar](20) NULL,
	[cInvalider] [varchar](20) NULL,
	[cVerifier] [varchar](20) NULL,
	[cBusType] [varchar](8) NULL,
	[bFirst] [bit] NOT NULL,
	[bInvalid] [bit] NOT NULL,
	[bVisible] [bit] NOT NULL,
	[iNetLock] [real] NULL,
	[citem_class] [varchar](2) NULL,
	[citemcode] [varchar](20) NULL,
	[cHeadCode] [varchar](15) NULL,
	[bDisFlag] [bit] NULL,
	[cDefine1] [varchar](20) NULL,
	[cDefine2] [varchar](20) NULL,
	[cDefine3] [varchar](20) NULL,
	[cDefine4] [datetime] NULL,
	[cDefine5] [int] NULL,
	[cDefine6] [datetime] NULL,
	[cDefine7] [float] NULL,
	[cDefine8] [varchar](4) NULL,
	[cDefine9] [varchar](8) NULL,
	[cDefine10] [varchar](60) NULL,
	[bPayMent] [bit] NULL,
	[iDisp] [smallint] NULL,
	[cCusName] [varchar](120) NULL,
	[cDLCode] [varchar](10) NULL,
	[cAccounter] [varchar](20) NULL,
	[cChecker] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[InventoryClass]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[InventoryClass](
	[cInvCCode] [varchar](12) NOT NULL,
	[cInvCName] [varchar](20) NOT NULL,
	[iInvCGrade] [tinyint] NOT NULL,
	[bInvCEnd] [bit] NULL,
	[cEcoCode] [varchar](2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Inventory]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Inventory](
	[cInvCode] [varchar](20) NOT NULL,
	[cInvAddCode] [varchar](25) NULL,
	[cInvName] [varchar](60) NOT NULL,
	[cInvStd] [varchar](30) NULL,
	[cInvCCode] [varchar](12) NULL,
	[cVenCode] [varchar](12) NULL,
	[cInvM_Unit] [varchar](8) NOT NULL,
	[cInvA_Unit] [varchar](8) NULL,
	[cReplaceItem] [varchar](20) NULL,
	[cPosition] [varchar](20) NULL,
	[bSale] [bit] NOT NULL,
	[bPurchase] [bit] NOT NULL,
	[bSelf] [bit] NOT NULL,
	[bComsume] [bit] NOT NULL,
	[bProducing] [bit] NOT NULL,
	[bService] [bit] NOT NULL,
	[bAccessary] [bit] NOT NULL,
	[iInvExchRate] [float] NULL,
	[iTaxRate] [smallint] NULL,
	[iInvWeight] [float] NULL,
	[iVolume] [float] NULL,
	[iInvRCost] [float] NULL,
	[iInvSPrice] [float] NULL,
	[iInvSCost] [float] NULL,
	[iInvLSCost] [float] NULL,
	[iInvNCost] [float] NULL,
	[iInvAdvance] [float] NULL,
	[iInvBatch] [float] NULL,
	[iSafeNum] [float] NULL,
	[iTopSum] [float] NULL,
	[iLowSum] [float] NULL,
	[iOverStock] [float] NULL,
	[cInvABC] [varchar](1) NULL,
	[bInvQuality] [bit] NOT NULL,
	[bInvBatch] [bit] NOT NULL,
	[bInvEntrust] [bit] NOT NULL,
	[bInvOverStock] [bit] NOT NULL,
	[dSDate] [datetime] NULL,
	[dEDate] [datetime] NULL,
	[bFree1] [bit] NOT NULL,
	[bFree2] [bit] NOT NULL,
	[cInvDefine1] [varchar](20) NULL,
	[cInvDefine2] [varchar](20) NULL,
	[cInvDefine3] [varchar](20) NULL,
	[I_id] [int] NOT NULL,
	[bInvType] [bit] NULL,
	[iInvMPCost] [float] NULL,
	[cQuality] [nvarchar](100) NULL,
	[iInvSaleCost] [float] NULL,
	[iInvSCost1] [float] NULL,
	[iInvSCost2] [float] NULL,
	[iInvSCost3] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Ctrt_Billdetail]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_Billdetail](
	[billDetailGUID] [uniqueidentifier] NOT NULL,
	[quantity] [int] NULL,
	[notaxAmount] [decimal](18, 2) NULL,
	[taxAmount] [decimal](18, 2) NULL,
	[billGUID] [nvarchar](50) NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_bus_m_billdetail] PRIMARY KEY CLUSTERED 
(
	[billDetailGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Ctrt_Bill]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_Bill](
	[billGUID] [uniqueidentifier] NOT NULL,
	[dDate] [datetime] NULL,
	[cBVCode] [nvarchar](50) NULL,
	[cCusCode] [nvarchar](50) NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
	[CustomerID] [uniqueidentifier] NULL,
	[cMemo] [nvarchar](200) NULL,
	[cInvCode] [nvarchar](50) NULL,
	[iQuantity] [decimal](18, 2) NULL,
	[iTaxUnitPrice] [decimal](18, 2) NULL,
	[iNatMoney] [decimal](18, 2) NULL,
	[iNatTax] [decimal](18, 2) NULL,
	[iNatSum] [decimal](18, 2) NULL,
	[iExchSum] [decimal](18, 2) NULL,
	[idDueDte] [datetime] NULL,
 CONSTRAINT [PK_bus_m_bill] PRIMARY KEY CLUSTERED 
(
	[billGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Ctrt_ContractGift]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_ContractGift](
	[GiftID] [uniqueidentifier] NOT NULL,
	[ContractID] [uniqueidentifier] NULL,
	[InstrumentID] [uniqueidentifier] NULL,
	[CreatedAt] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
	[Cost] [decimal](18, 2) NULL,
 CONSTRAINT [PK_Cms_Ctrt_ContractGift] PRIMARY KEY CLUSTERED 
(
	[GiftID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Sys_Role]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Sys_Role](
	[RoleID] [uniqueidentifier] NOT NULL,
	[RoleName] [nvarchar](50) NULL,
	[RoleDesc] [nvarchar](512) NULL,
	[IsSystem] [int] NULL,
	[LastModifyBy] [uniqueidentifier] NULL,
	[LastModifyOn] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [TM_Sys_Role_PK] PRIMARY KEY CLUSTERED 
(
	[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Sys_UserDepartment]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Sys_UserDepartment](
	[ID] [uniqueidentifier] NOT NULL,
	[UserID] [uniqueidentifier] NULL,
	[DepartmentID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Cms_Sys_UserDepartment] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Sys_User]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Sys_User](
	[UserID] [uniqueidentifier] NOT NULL,
	[Account] [nvarchar](50) NULL,
	[Password] [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
	[LastModifyBy] [uniqueidentifier] NULL,
	[LastModifyOn] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
	[Email] [nvarchar](50) NULL,
 CONSTRAINT [TM_Sys_Admin_PK] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[timedimension]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[timedimension](
	[timeid] [int] IDENTITY(1,1) NOT NULL,
	[thedate] [datetime] NULL,
	[theday] [nvarchar](15) NULL,
	[themonth] [nvarchar](15) NULL,
	[theyear] [smallint] NULL,
	[dayofmonth] [smallint] NULL,
	[weekofyear] [smallint] NULL,
	[monthofyear] [smallint] NULL,
	[quarter] [nvarchar](5) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Customer]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
	[cCusCode] [varchar](20) NOT NULL,
	[cCusName] [varchar](98) NULL,
	[cCusAbbName] [varchar](60) NOT NULL,
	[cCCCode] [varchar](12) NULL,
	[cDCCode] [varchar](12) NULL,
	[cTrade] [varchar](10) NULL,
	[cCusAddress] [varchar](98) NULL,
	[cCusPostCode] [varchar](6) NULL,
	[cCusRegCode] [varchar](18) NULL,
	[cCusBank] [varchar](30) NULL,
	[cCusAccount] [varchar](50) NULL,
	[dCusDevDate] [datetime] NULL,
	[cCusLPerson] [varchar](10) NULL,
	[cCusEmail] [varchar](30) NULL,
	[cCusPerson] [varchar](10) NULL,
	[cCusPhone] [varchar](20) NULL,
	[cCusFax] [varchar](20) NULL,
	[cCusBP] [varchar](20) NULL,
	[cCusHand] [varchar](20) NULL,
	[cCusPPerson] [varchar](10) NULL,
	[iCusDisRate] [float] NULL,
	[cCusCreGrade] [varchar](6) NULL,
	[iCusCreLine] [float] NULL,
	[iCusCreDate] [tinyint] NULL,
	[cCusPayCond] [varchar](20) NULL,
	[cCusOAddress] [varchar](40) NULL,
	[cCusOType] [varchar](10) NULL,
	[cCusHeadCode] [varchar](20) NULL,
	[cCusWhCode] [varchar](10) NULL,
	[cCusDepart] [varchar](12) NULL,
	[iARMoney] [float] NULL,
	[dLastDate] [datetime] NULL,
	[iLastMoney] [float] NULL,
	[dLRDate] [datetime] NULL,
	[iLRMoney] [float] NULL,
	[dEndDate] [datetime] NULL,
	[iFrequency] [int] NULL,
	[cCusDefine1] [varchar](20) NULL,
	[cCusDefine2] [varchar](20) NULL,
	[cCusDefine3] [varchar](20) NULL,
	[iCostGrade] [smallint] NULL,
	[Deleted] [bit] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Wfl_TaskUser]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Wfl_TaskUser](
	[ID] [uniqueidentifier] NOT NULL,
	[TaskID] [uniqueidentifier] NULL,
	[UserID] [uniqueidentifier] NULL,
	[CreatedAt] [datetime] NULL,
 CONSTRAINT [PK_Cms_Wfl_TaskUser] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Wfl_Task]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Wfl_Task](
	[TaskID] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](300) NULL,
	[Entry] [nvarchar](2000) NULL,
	[ActivityID] [uniqueidentifier] NULL,
	[Status] [int] NULL,
	[CreatedOn] [datetime] NULL,
	[TaskType] [int] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_CMS_WFL_TASK] PRIMARY KEY CLUSTERED 
(
	[TaskID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[VW_Report_ContractTrack]    Script Date: 04/04/2011 10:51:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[VW_Report_ContractTrack]  
as  
SELECT     A.CustomerID, B.Customername, A.InstrumentCategoryName, '' as FactSaleTrackURL,'承诺销售' AS Type, 'A' AS TypeSort, TrackYear, TrackMonth, PlanSaleAmount AS Amount  
FROM         [Cms_rpt_PutTrackStatistics] A JOIN  
                      Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID  
UNION ALL  
SELECT     A.CustomerID, B.Customername, A.InstrumentCategoryName, A.FactSaleTrackURL,'实际销售', 'B', TrackYear, TrackMonth, FactSaleAmount AS Amount  
FROM         [Cms_rpt_PutTrackStatistics] A JOIN  
                      Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID  
UNION ALL  
SELECT     A.CustomerID, B.Customername, A.InstrumentCategoryName, '','达标率', 'C', TrackYear, TrackMonth, Cast((PlanSaleAmount / FactSaleAmount)  as decimal(18,2)) AS Amount  
FROM         [Cms_rpt_PutTrackStatistics] A JOIN  
                      Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID
GO
/****** Object:  Table [dbo].[Cms_Wfl_Routing]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Wfl_Routing](
	[RoutingID] [uniqueidentifier] NOT NULL,
	[ModelID] [uniqueidentifier] NULL,
	[ActivityID] [uniqueidentifier] NULL,
	[PrevousActivityID] [uniqueidentifier] NULL,
	[NextActivityID] [uniqueidentifier] NULL,
	[RoutingOrder] [nvarchar](50) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_CMS_WFL_ROUTING] PRIMARY KEY CLUSTERED 
(
	[RoutingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Wfl_Instance]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Wfl_Instance](
	[InstanceID] [uniqueidentifier] NOT NULL,
	[ModelID] [uniqueidentifier] NULL,
	[InstanceName] [nvarchar](50) NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedAt] [datetime] NULL,
	[BussinessID] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_CMS_WFL_INSTANCE] PRIMARY KEY CLUSTERED 
(
	[InstanceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[Createtimedimension]    Script Date: 04/04/2011 10:52:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--drop PROCEDURE [dbo].[Createtimedimension] 
CREATE  PROCEDURE [dbo].[Createtimedimension] 
@bdate smalldatetime,
@edate smalldatetime
AS
BEGIN
SET NOCOUNT ON;
begin try
drop table [timedimension]
end try

begin catch
SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;

end catch

CREATE TABLE [dbo].[timedimension] (
   [timeid] [int] IDENTITY (1, 1) NOT NULL ,
   [thedate] [datetime] NULL ,
   [theday] [nvarchar] (15) NULL ,
   [themonth] [nvarchar] (15) NULL ,
   [theyear] [smallint] NULL ,
   [dayofmonth] [smallint] NULL ,
   [weekofyear] [smallint] NULL ,
   [monthofyear] [smallint] NULL ,
   [quarter] [nvarchar] (5) NULL

) ON [PRIMARY]

DECLARE @WeekString varchar(12),
		@dDate SMALLDATETIME,
		@sMonth varchar(20),
		@iYear smallint,
		@iDayOfMonth smallint,
		@iWeekOfYear smallint,
		@iMonthOfYear smallint,
		@sQuarter nvarchar(5),
		@sSQL varchar(100),
		@adddays int

SELECT @adddays = 1 --日期增量（可以自由设定）
SELECT @dDate = @bdate --开始日期

WHILE @dDate <= @edate --结束日期
BEGIN
   SELECT @WeekString = DATENAME (dw, @dDate)
   SELECT @sMonth=DATENAME(mm,@dDate)
   SELECT @iYear= DATENAME (yy, @dDate)
   SELECT @iDayOfMonth=DATENAME (dd, @dDate)
   SELECT @iWeekOfYear= DATENAME (week, @dDate)
   SELECT @iMonthOfYear=DATEPART(month, @dDate)
   SELECT @sQuarter = '第' + CAST(DATENAME (quarter, @dDate)as varchar(1))+'季度'
   INSERT INTO timedimension(thedate, theday, themonth, theyear,dayofmonth,weekofyear,monthofyear,quarter) 
   VALUES(@dDate, @WeekString, @sMonth, @iYear, @iDayOfMonth, @iWeekOfYear,@iMonthOfYear, @sQuarter)
   SELECT @dDate = @dDate + @adddays

END

END
GO
/****** Object:  Table [dbo].[Cms_Sys_RoleFunctionRelation]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Sys_RoleFunctionRelation](
	[ID] [uniqueidentifier] NOT NULL,
	[RoleID] [uniqueidentifier] NULL,
	[FuncID] [nvarchar](10) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [TR_Sys_RoleCodeRelation_PK] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Sys_MailAttachment]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[Cms_Sys_MailAttachment](
	[ID] [uniqueidentifier] NOT NULL,
	[MailMsgID] [uniqueidentifier] NULL,
	[AttachmentName] [varchar](500) NULL,
	[AttachmentData] [varbinary](max) NULL,
	[CreatedOn] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[LastModifyOn] [datetime] NULL,
	[LastModifyBy] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_MailAttachment] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cms_Ctrt_Contract]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_Contract](
	[ContractID] [uniqueidentifier] NOT NULL,
	[ContractCode] [sysname] NULL,
	[ContractAttribute] [uniqueidentifier] NULL,
	[ContractApplyDate] [datetime] NULL,
	[ContractEstimateSignDate] [datetime] NULL,
	[ContractType] [nvarchar](50) NULL,
	[ContractStatement] [nvarchar](500) NULL,
	[PaymentType] [tinyint] NULL,
	[PaymentPeriod] [int] NULL,
	[ContractTotalPrice] [decimal](18, 2) NULL,
	[ContractTotalCost] [decimal](18, 2) NULL,
	[GivenMaterialdesc] [nvarchar](200) NULL,
	[GivenMaterialCost] [decimal](18, 2) NULL,
	[CapitalCost] [decimal](18, 2) NULL,
	[RepairCost] [decimal](18, 2) NULL,
	[ContractProfit] [decimal](18, 2) NULL,
	[ContractProfitRate] [decimal](18, 2) NULL,
	[CustomerID] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[InstanceID] [uniqueidentifier] NULL,
	[Status] [int] NULL,
	[NextCheckers] [nvarchar](1000) NULL,
	[AssociationID] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[PutDiscount] [decimal](18, 2) NULL,
	[PutPaymentType] [int] NULL,
	[ReachTo] [decimal](18, 2) NULL,
	[Rebate] [decimal](18, 2) NULL,
 CONSTRAINT [PK_m_project] PRIMARY KEY CLUSTERED 
(
	[ContractID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Arm_UserPerformance]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Arm_UserPerformance](
	[ID] [uniqueidentifier] NOT NULL,
	[UserID] [uniqueidentifier] NULL,
	[Reward] [decimal](18, 2) NULL,
	[Fine] [decimal](18, 2) NULL,
	[BillID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_CMS_ARM_USERPERFORMANCE] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Arm_CustomerAge]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Arm_CustomerAge](
	[ID] [uniqueidentifier] NOT NULL,
	[CustomerID] [uniqueidentifier] NULL,
	[Age] [int] NULL,
	[Year] [int] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_CMS_ARM_CUSTOMERAGE] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Arm_CheckOffsForMonth]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Arm_CheckOffsForMonth](
	[CheckOffsID] [uniqueidentifier] NOT NULL,
	[Month] [int] NULL,
	[Year] [int] NULL,
	[CreatedOn] [datetime] NULL,
	[CheckOffsType] [uniqueidentifier] NULL,
	[UserID] [uniqueidentifier] NULL,
	[UnrealizedFunds] [decimal](18, 2) NULL,
	[RealizeFunds] [decimal](18, 2) NULL,
	[CreatedBy] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Cms_Arm_CheckOffsForMonth] PRIMARY KEY CLUSTERED 
(
	[CheckOffsID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[ImportInstrumentCategoryByLevel]    Script Date: 04/04/2011 10:52:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ImportInstrumentCategoryByLevel]
(
@cglevel int,
@parentcodelen int
)
AS
BEGIN
	INSERT INTO Cms_Ctrt_InstrumentCategory
	(
		categoryGUID,
		categoryCode,
		categoryName,
		isLastLevel,
		parentGUID
	)
	SELECT NEWID(),a.cInvCCode,a.cInvCName,a.bInvCEnd,b.categoryGUID FROM InventoryClass a
	INNER JOIN Cms_Ctrt_InstrumentCategory b ON b.categoryCode = LEFT(a.cInvCCode,@parentcodelen)
	WHERE a.iInvCGrade=@cglevel 
		AND cInvCCode NOT IN(
			SELECT categoryCode FROM Cms_Ctrt_InstrumentCategory)
END
GO
/****** Object:  Table [dbo].[Cms_Sys_UserRoleRelation]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Sys_UserRoleRelation](
	[ID] [uniqueidentifier] NOT NULL,
	[UserID] [uniqueidentifier] NULL,
	[RoleID] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [TR_Sys_AdminRoleRelation_PK] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Wfl_InstanceRouting]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Wfl_InstanceRouting](
	[TrackID] [uniqueidentifier] NOT NULL,
	[InstanceID] [uniqueidentifier] NULL,
	[ActivityID] [uniqueidentifier] NULL,
	[NextActivityID] [uniqueidentifier] NULL,
	[CheckBy] [uniqueidentifier] NULL,
	[CheckName] [nvarchar](50) NULL,
	[CheckStatus] [tinyint] NULL,
	[Comment] [nvarchar](1000) NULL,
	[CheckDate] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_CMS_WFL_TRACK] PRIMARY KEY CLUSTERED 
(
	[TrackID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Cms_Ctrt_ContractItem]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_ContractItem](
	[ItemID] [uniqueidentifier] NOT NULL,
	[ContractID] [uniqueidentifier] NULL,
	[ParentID] [uniqueidentifier] NULL,
	[InstrumentSequenceNum] [nvarchar](50) NULL,
	[CategoryCode] [nvarchar](50) NULL,
	[ItemModelNo] [nvarchar](50) NULL,
	[ItemStandard] [nvarchar](50) NULL,
	[PutPeriod] [int] NULL,
	[Unit] [nvarchar](10) NULL,
	[Count] [decimal](18, 2) NULL,
	[Price] [decimal](18, 2) NULL,
	[TotalPrice] [decimal](18, 2) NULL,
	[InstrumentCost] [decimal](18, 2) NULL,
	[RepairCost] [decimal](18, 2) NULL,
	[GuaranteeYear] [int] NULL,
	[LeasingRestrict] [int] NULL,
	[LeasingPrice] [decimal](18, 2) NULL,
	[PromiseRevenue] [decimal](18, 2) NULL,
	[Status] [int] NULL,
	[StartUsingDate] [datetime] NULL,
	[EndUsingDate] [datetime] NULL,
	[instrumentGUID] [uniqueidentifier] NULL,
	[IsCalcProfit] [bit] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_bus_m_contract_item] PRIMARY KEY CLUSTERED 
(
	[ItemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[SyncInstrumentCategory]    Script Date: 04/04/2011 10:52:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SyncInstrumentCategory]
AS
BEGIN 
	--同步存货大类（只做新增）
	DECLARE @cglevel int,		--编码级别
			@parentcodelen int	--父节点对应Code的长度
			
	SET @cglevel =2
	INSERT INTO Cms_Ctrt_InstrumentCategory
		(
			categoryGUID,
			categoryCode,
			categoryName,
			isLastLevel,
			parentGUID
		)
		SELECT NEWID(),a.cInvCCode,a.cInvCName,a.bInvCEnd,null FROM InventoryClass a
		WHERE iInvCGrade=@cglevel 
			AND cInvCCode NOT IN(
				SELECT categoryCode FROM Cms_Ctrt_InstrumentCategory)

	SET @cglevel =3
	SET @parentcodelen=4
	EXEC ImportInstrumentCategoryByLevel @cglevel,@parentcodelen

	SET @cglevel =4
	SET @parentcodelen=6
	EXEC ImportInstrumentCategoryByLevel @cglevel,@parentcodelen

	SET @cglevel =5
	SET @parentcodelen=8
	EXEC ImportInstrumentCategoryByLevel @cglevel,@parentcodelen


	--同步存货（只做新增） 
	INSERT INTO Cms_Ctrt_Instrument
	(
		instrumentGUID,
		instrumentCode,
		instrumentName,
		instrumentStandard,
		unit,
		vendor,
		CreatedOn,
		categoryGUID,
		[Type]
	)
	SELECT 
		NEWID(),a.cInvCode,a.cInvName,a.cInvStd,a.cInvM_Unit,a.cInvDefine3,a.dSDate,b.categoryGUID
		,CASE WHEN c.cInvCCode IS NULL THEN NULL ELSE cast(c.cInvCCode AS int) END AS ctype 
	FROM dbo.Inventory a
	LEFT JOIN Cms_Ctrt_InstrumentCategory b ON b.categoryCode = a.cInvCCode
	LEFT JOIN InventoryClass c ON c.cInvCCode = LEFT(b.categoryCode,2) AND c.iInvCGrade = 1
	WHERE a.cInvCode NOT IN 
		(
			SELECT instrumentCode FROM Cms_Ctrt_Instrument
		)
END
GO
/****** Object:  StoredProcedure [dbo].[UpdatePutContractPlan]    Script Date: 04/04/2011 10:52:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdatePutContractPlan]
@ContractID uniqueidentifier
AS
BEGIN
	DECLARE @CustomerID uniqueidentifier
		,@CategoryName nvarchar(20)
		,@TrackYear char(4)
		,@TrackMonth char(2)
		,@loopCount int			--循环次数：如果有效日期为1日则=投放年限*12，否则=投放年限*12+1
		,@PutPeriod int
		,@MonthPlanAmount money
		,@ContractBeginDate datetime
		,@MonthPlanIsHalf int		--收尾月份各取计划书的一半
		,@loop int
		,@PromiseRevenue decimal(18,2) 
		
	DECLARE cur_Contract CURSOR FOR 	
	SELECT 
		c.CustomerID
		,ic.categoryName
		,c.StartDate
		,ci.PutPeriod
		,ci.PromiseRevenue
	FROM Cms_Ctrt_Contract c WITH(nolock)
	INNER JOIN Cms_Ctrt_ContractItem ci WITH(nolock) ON ci.ContractID = c.ContractID
	INNER JOIN Cms_Ctrt_InstrumentCategory ic WITH(nolock) ON ic.categoryCode = ci.CategoryCode
	WHERE c.ContractID = @ContractID AND
		c.ContractType = 1 and left(ci.CategoryCode,2) = '02' --and PutPeriod > 0
		AND ci.ParentID IS NULL  and c.StartDate IS NOT NULL
		
	OPEN cur_Contract
	FETCH next FROM cur_Contract
	INTO @CustomerID,@CategoryName,@ContractBeginDate,@PutPeriod,@PromiseRevenue
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @loopCount = 0
		IF (datepart(day,@ContractBeginDate)>1)
			BEGIN 
				SET @loopCount = @PutPeriod * 12 +1
				SET @MonthPlanIsHalf = 1
			END 
		ELSE
			BEGIN 
				SET @loopCount = @PutPeriod * 12
				SET @MonthPlanIsHalf = 0
			END
		SET @loop = 1
		WHILE @loop <=@loopCount
		BEGIN
			IF (@MonthPlanIsHalf =1 AND (@loop = 1 OR @loop = @loopCount ))
				SET @MonthPlanAmount = (@PromiseRevenue / 12)/2
			ELSE
					SET @MonthPlanAmount = @PromiseRevenue / 12
			SET @TrackYear = cast(datepart(year,@ContractBeginDate) AS char(4))
			SET @TrackMonth = Right('00' + Convert(VarChar(2),datepart(month,@ContractBeginDate)),2)
			INSERT INTO Cms_rpt_PutContractPlan
			(
				CustomerID,
				ContractID,
				InstrumentCategoryName,
				TrackYear,
				TrackMonth,
				PlanSaleAmount
			)
			VALUES
			(
				@CustomerID,
				@ContractID,
				@CategoryName,
				@TrackYear,
				@TrackMonth,
				@MonthPlanAmount
			)
			SET @ContractBeginDate = dateadd(month,1,@ContractBeginDate)
			SET @loop = @loop+1
		END 
		
		FETCH next FROM cur_Contract
		INTO @CustomerID,@CategoryName,@ContractBeginDate,@PutPeriod,@PromiseRevenue
	END
	
	CLOSE cur_Contract
	DEALLOCATE cur_Contract
END
GO
/****** Object:  Table [dbo].[Cms_Ctrt_ItemGuaranty]    Script Date: 04/04/2011 10:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cms_Ctrt_ItemGuaranty](
	[GuarantyID] [uniqueidentifier] NOT NULL,
	[ItemID] [uniqueidentifier] NULL,
	[endDate] [datetime] NULL,
	[guarantyType] [int] NULL,
	[guarantyStatus] [int] NULL,
	[startDate] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_bus_m__item_guaranty] PRIMARY KEY CLUSTERED 
(
	[GuarantyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Default [DF_Cms_Ctrt_Customer_CustomerID]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Ctrt_Customer] ADD  CONSTRAINT [DF_Cms_Ctrt_Customer_CustomerID]  DEFAULT (newid()) FOR [CustomerID]
GO
/****** Object:  Default [DF_Cms_Ctrt_InstrumentCategory_categoryGUID]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Ctrt_InstrumentCategory] ADD  CONSTRAINT [DF_Cms_Ctrt_InstrumentCategory_categoryGUID]  DEFAULT (newid()) FOR [categoryGUID]
GO
/****** Object:  Default [DF__Cms_rpt_Put__RID__5EDF0F2E]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Rpt_PutContractPlan] ADD  DEFAULT (newid()) FOR [RID]
GO
/****** Object:  Default [DF__Cms_rpt_Put__RID__592635D8]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Rpt_PutTrackStatistics] ADD  CONSTRAINT [DF__Cms_rpt_Put__RID__592635D8]  DEFAULT (newid()) FOR [RID]
GO
/****** Object:  Default [DF_Cms_Sys_Desktop_DesktopGUID]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_Desktop] ADD  CONSTRAINT [DF_Cms_Sys_Desktop_DesktopGUID]  DEFAULT (newid()) FOR [DesktopID]
GO
/****** Object:  Default [DF_Cms_Desktop_WebParts_RowID]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_DesktopWebParts] ADD  CONSTRAINT [DF_Cms_Desktop_WebParts_RowID]  DEFAULT (newid()) FOR [RowID]
GO
/****** Object:  Default [DF__TC_Sys_Di__Delet__54EB90A0]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_Dictionary] ADD  CONSTRAINT [DF__TC_Sys_Di__Delet__54EB90A0]  DEFAULT ((0)) FOR [Deleted]
GO
/****** Object:  Default [DF_Cms_Sys_Log_b]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_Log] ADD  CONSTRAINT [DF_Cms_Sys_Log_b]  DEFAULT (newid()) FOR [logGUID]
GO
/****** Object:  Default [DF_Cms_Sys_Mail_MailMsgID]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_Mail] ADD  CONSTRAINT [DF_Cms_Sys_Mail_MailMsgID]  DEFAULT (newid()) FOR [MailMsgID]
GO
/****** Object:  Default [DF_Cms_Sys_Mail_Status]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_Mail] ADD  CONSTRAINT [DF_Cms_Sys_Mail_Status]  DEFAULT ((0)) FOR [Status]
GO
/****** Object:  Default [DF_Cms_Sys_Mail_CreateTime]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_Mail] ADD  CONSTRAINT [DF_Cms_Sys_Mail_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
GO
/****** Object:  Default [DF_MailAttachment_ID]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_MailAttachment] ADD  CONSTRAINT [DF_MailAttachment_ID]  DEFAULT (newid()) FOR [ID]
GO
/****** Object:  Default [DF_TR_Sys_RoleCodeRelation_ID]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_RoleFunctionRelation] ADD  CONSTRAINT [DF_TR_Sys_RoleCodeRelation_ID]  DEFAULT (newid()) FOR [ID]
GO
/****** Object:  Default [DF__Cms_Wfl_A__Activ__762C88DA]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_Activity] ADD  DEFAULT (newid()) FOR [ActivityID]
GO
/****** Object:  Default [DF__Cms_Wfl_I__Insta__7908F585]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_Instance] ADD  DEFAULT (newid()) FOR [InstanceID]
GO
/****** Object:  Default [DF__Cms_Wfl_T__Track__047AA831]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_InstanceRouting] ADD  CONSTRAINT [DF__Cms_Wfl_T__Track__047AA831]  DEFAULT (newid()) FOR [TrackID]
GO
/****** Object:  Default [DF__Cms_Wfl_M__Model__7BE56230]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_Model] ADD  DEFAULT (newid()) FOR [ModelID]
GO
/****** Object:  Default [DF__Cms_Wfl_R__Routi__7EC1CEDB]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_Routing] ADD  DEFAULT (newid()) FOR [RoutingID]
GO
/****** Object:  Default [DF__Cms_Wfl_T__TaskI__019E3B86]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_Task] ADD  CONSTRAINT [DF__Cms_Wfl_T__TaskI__019E3B86]  DEFAULT (newid()) FOR [TaskID]
GO
/****** Object:  Default [DF_Cms_Wfl_Task_CreatedOn]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_Task] ADD  CONSTRAINT [DF_Cms_Wfl_Task_CreatedOn]  DEFAULT (getdate()) FOR [CreatedOn]
GO
/****** Object:  Default [DF_Cms_Wfl_Task_TaskType]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_Task] ADD  CONSTRAINT [DF_Cms_Wfl_Task_TaskType]  DEFAULT ((0)) FOR [TaskType]
GO
/****** Object:  Default [DF_Cms_Wfl_TaskUser_CreatedAt]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_TaskUser] ADD  CONSTRAINT [DF_Cms_Wfl_TaskUser_CreatedAt]  DEFAULT (getdate()) FOR [CreatedAt]
GO
/****** Object:  ForeignKey [FK_Cms_Arm_CheckOffsForMonth_Cms_Sys_User]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Arm_CheckOffsForMonth]  WITH CHECK ADD  CONSTRAINT [FK_Cms_Arm_CheckOffsForMonth_Cms_Sys_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[Cms_Sys_User] ([UserID])
GO
ALTER TABLE [dbo].[Cms_Arm_CheckOffsForMonth] CHECK CONSTRAINT [FK_Cms_Arm_CheckOffsForMonth_Cms_Sys_User]
GO
/****** Object:  ForeignKey [FK_Cms_Arm_CustomerAge_Cms_Ctrt_Customer]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Arm_CustomerAge]  WITH CHECK ADD  CONSTRAINT [FK_Cms_Arm_CustomerAge_Cms_Ctrt_Customer] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Cms_Ctrt_Customer] ([CustomerID])
GO
ALTER TABLE [dbo].[Cms_Arm_CustomerAge] CHECK CONSTRAINT [FK_Cms_Arm_CustomerAge_Cms_Ctrt_Customer]
GO
/****** Object:  ForeignKey [FK_Cms_Arm_UserPerformance_Cms_Ctrt_Bill]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Arm_UserPerformance]  WITH CHECK ADD  CONSTRAINT [FK_Cms_Arm_UserPerformance_Cms_Ctrt_Bill] FOREIGN KEY([BillID])
REFERENCES [dbo].[Cms_Ctrt_Bill] ([billGUID])
GO
ALTER TABLE [dbo].[Cms_Arm_UserPerformance] CHECK CONSTRAINT [FK_Cms_Arm_UserPerformance_Cms_Ctrt_Bill]
GO
/****** Object:  ForeignKey [FK_Cms_Arm_UserPerformance_Cms_Sys_User]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Arm_UserPerformance]  WITH CHECK ADD  CONSTRAINT [FK_Cms_Arm_UserPerformance_Cms_Sys_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[Cms_Sys_User] ([UserID])
GO
ALTER TABLE [dbo].[Cms_Arm_UserPerformance] CHECK CONSTRAINT [FK_Cms_Arm_UserPerformance_Cms_Sys_User]
GO
/****** Object:  ForeignKey [FK_Contract_Customer]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Ctrt_Contract]  WITH CHECK ADD  CONSTRAINT [FK_Contract_Customer] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Cms_Ctrt_Customer] ([CustomerID])
GO
ALTER TABLE [dbo].[Cms_Ctrt_Contract] CHECK CONSTRAINT [FK_Contract_Customer]
GO
/****** Object:  ForeignKey [FK_ContractItem_Contract]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Ctrt_ContractItem]  WITH CHECK ADD  CONSTRAINT [FK_ContractItem_Contract] FOREIGN KEY([ContractID])
REFERENCES [dbo].[Cms_Ctrt_Contract] ([ContractID])
GO
ALTER TABLE [dbo].[Cms_Ctrt_ContractItem] CHECK CONSTRAINT [FK_ContractItem_Contract]
GO
/****** Object:  ForeignKey [FK_Item_Contract_Cuaranty]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Ctrt_ItemGuaranty]  WITH CHECK ADD  CONSTRAINT [FK_Item_Contract_Cuaranty] FOREIGN KEY([ItemID])
REFERENCES [dbo].[Cms_Ctrt_ContractItem] ([ItemID])
GO
ALTER TABLE [dbo].[Cms_Ctrt_ItemGuaranty] CHECK CONSTRAINT [FK_Item_Contract_Cuaranty]
GO
/****** Object:  ForeignKey [FK_TC_Sys_Dictionary_TC_Sys_Dictionary]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_Dictionary]  WITH CHECK ADD  CONSTRAINT [FK_TC_Sys_Dictionary_TC_Sys_Dictionary] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Cms_Sys_Dictionary] ([ID])
GO
ALTER TABLE [dbo].[Cms_Sys_Dictionary] CHECK CONSTRAINT [FK_TC_Sys_Dictionary_TC_Sys_Dictionary]
GO
/****** Object:  ForeignKey [FK_MailAttachment_Cms_Sys_MailAttachment]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_MailAttachment]  WITH CHECK ADD  CONSTRAINT [FK_MailAttachment_Cms_Sys_MailAttachment] FOREIGN KEY([MailMsgID])
REFERENCES [dbo].[Cms_Sys_Mail] ([MailMsgID])
GO
ALTER TABLE [dbo].[Cms_Sys_MailAttachment] CHECK CONSTRAINT [FK_MailAttachment_Cms_Sys_MailAttachment]
GO
/****** Object:  ForeignKey [FK_TR_SYS_R_REFERENCE_TM_SYS_F]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_RoleFunctionRelation]  WITH CHECK ADD  CONSTRAINT [FK_TR_SYS_R_REFERENCE_TM_SYS_F] FOREIGN KEY([FuncID])
REFERENCES [dbo].[Cms_Sys_Function] ([FuncID])
GO
ALTER TABLE [dbo].[Cms_Sys_RoleFunctionRelation] CHECK CONSTRAINT [FK_TR_SYS_R_REFERENCE_TM_SYS_F]
GO
/****** Object:  ForeignKey [TM_Sys_Role_TR_Sys_RoleCodeRelation_FK1]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_RoleFunctionRelation]  WITH CHECK ADD  CONSTRAINT [TM_Sys_Role_TR_Sys_RoleCodeRelation_FK1] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Cms_Sys_Role] ([RoleID])
GO
ALTER TABLE [dbo].[Cms_Sys_RoleFunctionRelation] CHECK CONSTRAINT [TM_Sys_Role_TR_Sys_RoleCodeRelation_FK1]
GO
/****** Object:  ForeignKey [TM_Sys_Admin_TR_Sys_AdminRoleRelation_FK1]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_UserRoleRelation]  WITH CHECK ADD  CONSTRAINT [TM_Sys_Admin_TR_Sys_AdminRoleRelation_FK1] FOREIGN KEY([UserID])
REFERENCES [dbo].[Cms_Sys_User] ([UserID])
GO
ALTER TABLE [dbo].[Cms_Sys_UserRoleRelation] CHECK CONSTRAINT [TM_Sys_Admin_TR_Sys_AdminRoleRelation_FK1]
GO
/****** Object:  ForeignKey [TM_Sys_Role_TR_Sys_AdminRoleRelation_FK1]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Sys_UserRoleRelation]  WITH CHECK ADD  CONSTRAINT [TM_Sys_Role_TR_Sys_AdminRoleRelation_FK1] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Cms_Sys_Role] ([RoleID])
GO
ALTER TABLE [dbo].[Cms_Sys_UserRoleRelation] CHECK CONSTRAINT [TM_Sys_Role_TR_Sys_AdminRoleRelation_FK1]
GO
/****** Object:  ForeignKey [FK_Wfl_Instance_Model]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_Instance]  WITH CHECK ADD  CONSTRAINT [FK_Wfl_Instance_Model] FOREIGN KEY([ModelID])
REFERENCES [dbo].[Cms_Wfl_Model] ([ModelID])
GO
ALTER TABLE [dbo].[Cms_Wfl_Instance] CHECK CONSTRAINT [FK_Wfl_Instance_Model]
GO
/****** Object:  ForeignKey [FK_BUS_Instance_Track]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_InstanceRouting]  WITH CHECK ADD  CONSTRAINT [FK_BUS_Instance_Track] FOREIGN KEY([InstanceID])
REFERENCES [dbo].[Cms_Wfl_Instance] ([InstanceID])
GO
ALTER TABLE [dbo].[Cms_Wfl_InstanceRouting] CHECK CONSTRAINT [FK_BUS_Instance_Track]
GO
/****** Object:  ForeignKey [FK_Wfl_Routing_Activity]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_Routing]  WITH NOCHECK ADD  CONSTRAINT [FK_Wfl_Routing_Activity] FOREIGN KEY([ActivityID])
REFERENCES [dbo].[Cms_Wfl_Activity] ([ActivityID])
GO
ALTER TABLE [dbo].[Cms_Wfl_Routing] CHECK CONSTRAINT [FK_Wfl_Routing_Activity]
GO
/****** Object:  ForeignKey [FK_Wfl_Routing_Model]    Script Date: 04/04/2011 10:51:58 ******/
ALTER TABLE [dbo].[Cms_Wfl_Routing]  WITH NOCHECK ADD  CONSTRAINT [FK_Wfl_Routing_Model] FOREIGN KEY([ModelID])
REFERENCES [dbo].[Cms_Wfl_Model] ([ModelID])
GO
ALTER TABLE [dbo].[Cms_Wfl_Routing] CHECK CONSTRAINT [FK_Wfl_Routing_Model]
GO